package com.testmanager.testmananger.shuiyou.common.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.testmanager.testmananger.foundation.util.FileUtils;
import com.testmanager.testmananger.shuiyou.common.Constant.SqlEnv;
import com.testmanager.testmananger.shuiyou.common.dto.ConnOtherDbDto;
import com.testmanager.testmananger.shuiyou.common.myAnnotation.MyColumn;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

@Slf4j
@Configuration
public class OtherDataSource {
	
	public static final SqlEnv YUNDUAN = SqlEnv.YUNDUAN;
	public static final SqlEnv JUNFAN = SqlEnv.JUFAN;
	public static final String TEST = "test";


	  public static final String mysqldriver = "com.mysql.jdbc.Driver"; // mysql数据库的驱动类//com.mysql.cj.jdbc.Driver   com.mysql.jdbc.Driver
	    public static final String oracledriver = "oracle.jdbc.OracleDriver"; // oracles数据库的驱动类
	    public static final String sql2005driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // sqlserver数据库的驱动类
	    public static final String sql2000driver = "net.sourceforge.jtds.jdbc.Driver"; // sqlserver数据库的驱动类
	    
	   // public DataSource  dataSource;
	
	public  DataSource createDataSource(String key, String driveClass, String url, String username, String password, String databasetype) {
        try {
            try { // 排除连接不上的错误
                Class.forName(driveClass);
                DriverManager.getConnection(url, username, password);// 相当于连接数据库
            } catch (Exception e) {
            //	throw new Exception(e);
                log.error("errer43:{}",e);
                return null;
            }
            @SuppressWarnings("resource")
            DruidDataSource druidDataSource = new DruidDataSource();

            druidDataSource.setName(key);
            druidDataSource.setDriverClassName(driveClass);
            druidDataSource.setUrl(url);
            druidDataSource.setUsername(username);
            druidDataSource.setPassword(password);
            druidDataSource.setInitialSize(5); //初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
            druidDataSource.setMaxActive(200); //最大连接池数量
            druidDataSource.setMaxWait(60000); //获取连接时最大等待时间，单位毫秒。当链接数已经达到了最大链接数的时候，应用如果还要获取链接就会出现等待的现象，等待链接释放并回到链接池，如果等待的时间过长就应该踢掉这个等待，不然应用很可能出现雪崩现象
            druidDataSource.setMinIdle(5); //最小连接池数量
            String validationQuery = "select 1 from dual";
            if("mysql".equalsIgnoreCase(databasetype)) {
                driveClass = mysqldriver;
                validationQuery = "select 1";
            } else if("oracle".equalsIgnoreCase(databasetype)){
                driveClass = oracledriver;
                druidDataSource.setPoolPreparedStatements(true); //是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
                druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(50);
                int sqlQueryTimeout = 15000;
                druidDataSource.setConnectionProperties("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout="+sqlQueryTimeout);//对于耗时长的查询sql，会受限于ReadTimeout的控制，单位毫秒
            } else if("sqlserver2000".equalsIgnoreCase(databasetype)){
                driveClass = sql2000driver;
                validationQuery = "select 1";
            } else if("sqlserver".equalsIgnoreCase(databasetype)){
                driveClass = sql2005driver;
                validationQuery = "select 1";
            }

            druidDataSource.setTestOnBorrow(true); //申请连接时执行validationQuery检测连接是否有效，这里建议配置为TRUE，防止取到的连接不可用
            druidDataSource.setTestWhileIdle(true);//建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
            druidDataSource.setValidationQuery(validationQuery); //用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
            druidDataSource.setFilters("stat");//属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
            druidDataSource.setTimeBetweenEvictionRunsMillis(60000); //配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
            druidDataSource.setMinEvictableIdleTimeMillis(180000); //配置一个连接在池中最小生存的时间，单位是毫秒，这里配置为3分钟180000
            druidDataSource.setKeepAlive(true); //打开druid.keepAlive之后，当连接池空闲时，池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作，即执行druid.validationQuery指定的查询SQL，一般为select * from dual，只要minEvictableIdleTimeMillis设置的小于防火墙切断连接时间，就可以保证当连接空闲时自动做保活检测，不会被防火墙切断

            druidDataSource.setRemoveAbandoned(true); //是否移除泄露的连接/超过时间限制是否回收。
            druidDataSource.setRemoveAbandonedTimeout(3600); //泄露连接的定义时间(要超过最大事务的处理时间)；单位为秒。这里配置为1小时
            druidDataSource.setLogAbandoned(true); ////移除泄露连接发生是是否记录日志

            DataSource createDataSource = (DataSource) druidDataSource;
            druidDataSource.init();
             return createDataSource;
            
           // druidDataSource.get
          //  Map<Object, Object> dynamicTargetDataSources_temp = this.dynamicTargetDataSources;
         //   dynamicTargetDataSources_temp.put(key, createDataSource);// 加入map
          //  setTargetDataSources(dynamicTargetDataSources_temp);// 将map赋值给父类的TargetDataSources
           // super.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理
          //  log.info(key+"数据源初始化成功");
            //log.info(key+"数据源的概况："+druidDataSource.dump());
           // return true;
        } catch (Exception e) {
           // log.error(e + "");
            log.error("error:{}",e);
            return null;
        }
    }

    @Bean(name = "datasources")
    public Map<String,DataSource> getDataSourceMap(){
	    Map datasources = new HashMap();
//	    datasources.put(JUNFAN,getDruidMysqlDataSource());
//        datasources.put(YUNDUAN,getDruidYunduanDataSource());
        log.info("线程池：{}",datasources);
	    return  datasources;
    }


    private DataSource getDruidMysqlDataSource(){
//        String username = "root";
//	    String password = "123456";
//	    String key = "test1";
//	    String driveClass = mysqldriver;
//	    String databasetype = "mysql";
//	    String url = "jdbc:mysql://47.97.7.224:3306/test2?useSSL=false&useTimezone=true&serverTimezone=GMT%2B8&useUnicode=true&amp&characterEncoding=utf8";
//
	    String username = "j2_szjk";
	    String password = "j2_szjk";
	    String key = "j2_szjk";
	    String driveClass = oracledriver;
	    String databasetype = "oracle";
	    String url = "jdbc:oracle:thin:@10.199.138.30:1521/syzbj2yy";  //10.199.138.30/syzbj2yy_sjb
		//OtherDataSource dynamicDataSource = new OtherDataSource();



        log.info("初始化局端");
		return createDataSource(key, driveClass, url, username, password, databasetype);

		//DruidDataSourceFactory.cre
	   // return  dynamicDataSource.dataSource;
    }

    private DataSource getDruidYunduanDataSource(){
        String username = "root";
	    String password = "123456";
	    String key = "test1";
	    String driveClass = mysqldriver;
	    String databasetype = "mysql";
	    String url = "jdbc:mysql://192.168.149.25:3306/sdssbjk2020?useSSL=false&useTimezone=true&serverTimezone=GMT%2B8&useUnicode=true&amp&characterEncoding=utf8";
		//OtherDataSource dynamicDataSource = new OtherDataSource();
        log.info("初始化云端");
		return createDataSource(key, driveClass, url, username, password, databasetype);

		//DruidDataSourceFactory.cre
	    //return  dynamicDataSource.dataSource;
    }

    //Connection connection = dynamicDataSource.dataSource.getConnection();

//    boolean accessible = false;
//        for (Field field : fields){
//        accessible = field.isAccessible();
//        field.setAccessible(true);//取消访问检察
//        Class o = field.getType();
//        // field.get
//        Object value = map.get(field.getName());
//        System.out.println(Objects.equals(o,int.class));
//        if (Objects.equals(o,int.class)|| Objects.equals(o,long.class)||Objects.equals(o,float.class)||Objects.equals(o,short.class)){
//            value = null == value ?0:value;
//        }
//        field.set(zipBean,value);
//        field.setAccessible(accessible);



    //        DataSource dataSource = otherDataSource.getDataSourceMap().get(otherDataSource.JUNFAN);
//        //log.info("datasources:{}",otherDataSource.getDataSourceMap());
//        //log.info("datasource:{}",otherDataSource.getDataSourceMap().get("junfan"));
//       // log.info("datasoure:{}",dataSource);
//            conn = dataSource.getConnection();
//            String sql = "select * from sy_test2";
//            preparedStatement =  conn.prepareStatement(sql);
//            resultSet = preparedStatement.executeQuery();
//            while (resultSet.next()){
//                ConnOtherDbDto connOtherDbDto = new ConnOtherDbDto();
//                connOtherDbDto.setId( resultSet.getInt("id"))
//                        .setTest(resultSet.getString("test"))
//                        .setName(resultSet.getString("name"));
//                list.add(connOtherDbDto);
//
//            }

    public List<Map<String,String>> getSelectResult(String dbname,String sql) throws SQLException {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List list = new ArrayList();

	    try{
	        if (null == getDataSourceMap().get(dbname)){
	            log.info("连接澉不存在：{}",dbname);
	            return null;
            }
	        conn = getDataSourceMap().get(dbname).getConnection();
            log.info("查询：dbname:{},sql:{}",dbname,sql);
            preparedStatement =  conn.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int colunm = resultSet.getMetaData().getColumnCount();
                Map<String,String> p = new HashMap();
                for(int i =1;i<=colunm;i++){
                    p.put(resultSet.getMetaData().getColumnName(i),resultSet.getString(i));
                }
                list.add(p);
            }
            return list;

        } catch (Exception e){
            e.printStackTrace();
            throw e;
            //return null;
        }finally {

            try {
                resultSet.close();
                preparedStatement.close();
                conn.close();
            } catch (Exception e) {
                // e.printStackTrace();
            }
        }


    }



    public <T> List<T> getSelectResult(SqlEnv dbname, String sql, Class<T> clazz){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List list = new ArrayList();
       // boolean isMyAno = false;
        Field[] fields = clazz.getDeclaredFields();
        boolean accessible = false;

        try {
            conn = getDataSourceMap().get(dbname).getConnection();
            log.info("查询：dbname:{},sql:{}",dbname.getName(),sql);
            preparedStatement =  conn.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
              //  Map map = new HashMap();
               T newbean =  clazz.newInstance();
                for (Field field : fields){
                    accessible = field.isAccessible();
                    //isMyAno = field.isAnnotationPresent(MyColumn.class);
                    Object value = getdbResult(resultSet,field);
                    Class o = field.getType();
                    if (Objects.equals(o,int.class)|| Objects.equals(o,long.class)||Objects.equals(o,float.class)||Objects.equals(o,short.class)){
                        value = null == value ?0:value;
                    }
                    field.setAccessible(true);
                    field.set(newbean,value);
                    field.setAccessible(accessible);
                   // map.put(field.getName(),getdbResult(resultSet,field));
                   // field.set
                }
                list.add(newbean);
            }
            return list;
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {

            try {
                resultSet.close();
                preparedStatement.close();
                conn.close();
            } catch (Exception e) {
                // e.printStackTrace();
            }
        }
    }

    public boolean modifysql(SqlEnv dbname,String sql) {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = getDataSourceMap().get(dbname).getConnection();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.executeUpdate();
            flag = true;

        } catch (Exception e)
        {
            log.info("修改数据库时出现错误！！");
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }


    private Object getdbResult(ResultSet resultSet,Field field) throws SQLException {
	    boolean flag = field.isAnnotationPresent(MyColumn.class);
	    String searchName = flag?field.getAnnotation(MyColumn.class).name():field.getName();
	    if (Objects.equals(field.getType(),String.class) ){
	        return resultSet.getString(searchName);
        }
        if (Objects.equals(field.getType(),int.class)){
            return resultSet.getInt(searchName);
        }
        if (Objects.equals(field.getType(),long.class)){
            return resultSet.getLong(searchName);
        }
        return resultSet.getString(searchName);
    }






	
	
	
}
